|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Design
The main goals in designing the physical data layout of a WebServer system are to provide balanced I/Os across all the disks that are randomly accessed and to isolate the sequential I/Os. In a WebServer system, you know that data is generally accessed in random fashion, but some of the data rows may be very large, spanning several blocks.
For most systems, you rely on the number of updates to determine whether it is worth it to separate the redo logs from the data volumes to take advantage of the sequential nature of its I/Os. For a WebServer system, this is a fairly hard call; without knowing the specific nature of your WebServer system, it is difficult to make a recommendation.
TIP: Without knowing the specifics of your system, I have to recommend that you separate the redo logs onto their own RAID-1 volume. Even if you are not performing a large number of updates, doing so certainly wont hurt your systems performance.
As with all transaction types, it is important to have a sufficient number of disk drives to handle the I/O generated by these transactions. It is important to monitor the systems I/O rates to ensure that the I/O subsystem is not overloaded during peak times.
The layout for this type of system looks more like an OLTP system than anything else. Because of the unknown nature of the log activity associated with this type of system, you may be able to minimize the size of the log volume and share the archive log volume with the OS or other disk volumes. A minimal configuration should look something like this:
|
Element (# of Disks)
| Comments
|
|
System (1+)
| The system disk is used for the operating system, swap file (if applicable), user files, and Oracle binaries. If possible, mirror this disk for additional fault tolerance. You can put the archive log files here if archiving is infrequent.
|
Redo log (1+)
| You should have at least one log volume. This volume should be made up of at least two physical disks using RAID-1. By using only one log volume, performance is degraded during archiving because the sequential nature of the log writes is disrupted.
|
Archive logs (1+)
| The number of disks needed for the archive log files is determined by the amount of data you have to archive. This data can be written to tape as necessary. If the amount and frequency of archiving is small, you can use the OS volume for archive logs.
|
Data and index (1+)
| Because you always get concurrent access to disks with a disk array, it is not necessary to split the data and indexes into separate volumes. The number of disks you need for data and index is determined by the amount of random I/O your user community generates.
|
|
Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks.
I recommend taking advantage of a disk array rather than relying on Oracle striping. A hardware disk array provides you with the highest level of performance and has the added benefit of fault tolerance, if you need it.
Because of the nature of the WebServer, you may require a lot of fault tolerance. If your update activity is low, you may benefit from the use of RAID-5. Remember that RAID-5 has a high overhead for writes, but not for reads.
Tuning
The design of the WebServer system tends to follow that of the OLTP system, with the exception of the database block size, which looks more like that of the DSS system. When tuning a WebServer system, analyze the system to see whether adjustments to these parameters are necessary:
- DB_BLOCK_BUFFERS. Block buffers are probably the most critical area in the WebServer system. Having enough block buffers cuts down on the number of I/Osand hot Web pages may get a very good cache-hit rate.
- Library cache. Check the V$LIBRARYCACHE table, which contains statistics on how well you are using the library cache. If necessary, you may have to increase the size of the shared pool.
- Open cursors. You may have to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS.
- Cursor space for time. If you have plenty of memory, you can speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME to TRUE.
- Spin counts. By tuning the Oracle initialization parameter SPIN_COUNT to enable spinning on resources (that is, the process spins instead of going to sleep while waiting for a resource to become available), you may see improved performance. SPIN_COUNT is useful when you have multiple CPUs and short-running transactions.
- Data dictionary cache. To check the efficiency of the data dictionary cache, look at the dynamic performance table V$ROWCACHE. If necessary, you may have to increase the shared pool size.
- Rollback contention. Rollback contention should not be an issue in the WebServer system.
- Latch contention. Latch contention should not be much of an issue either.
- Checkpoints. As with rollbacks segments and latches, I dont think that checkpoints will be an issue.
- Archiving. By now, you should be convinced that you should always run with archiving enabled. By having a sufficiently large redo log, you may be able to put off archiving until off hours, especially if the number of updates is low.
As you can see, most of these recommendations are very similar to those given for the OLTP system.
|